package com.testingedu.jdbcDao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.testingedu.common.QueryResult;
import com.testingedu.db.DBManager;
import com.testingedu.model.TTrainInfo;
import com.testingedu.utils.Format;

public class TTrainInfoDao extends BaseDao<TTrainInfo> {
	public TTrainInfoDao() {
		clazz = TTrainInfo.class;
	}

	/**
	 * 添加培训资讯
	 * 
	 * @param tbTrainInfo
	 * @return
	 */
	public int add(TTrainInfo tbTrainInfo) throws Exception {
		String sql = "insert into t_train_info(title,content,keyword_1,keyword_2,keyword_3,category_id,user_id,add_time,level,top) value(?,?,?,?,?,?,?,?,?,?)";
		List<Object> values = new ArrayList<Object>();
		values.add(tbTrainInfo.getTitle());
		//System.out.println("1:title:" + tbTrainInfo.getTitle());
		values.add(tbTrainInfo.getContent().trim());
		//System.out.println("2:context:" + tbTrainInfo.getContent());
		values.add(tbTrainInfo.getKeyword1());
		//System.out.println("3:key1:" + tbTrainInfo.getKeyword1());
		values.add(tbTrainInfo.getKeyword2());
		//System.out.println("4:key2:" + tbTrainInfo.getKeyword2());
		values.add(tbTrainInfo.getKeyword3());
		//System.out.println("5:key1:" + tbTrainInfo.getKeyword3());
		values.add(tbTrainInfo.gettCategory().getId());
		//System.out.println("6:category:" + tbTrainInfo.gettCategory().getId());
		values.add(tbTrainInfo.gettUser().getId());
		//System.out.println("7:user:" + tbTrainInfo.gettUser().getId());
		values.add(Format.formatDateDay(new Date()));
		//System.out.println("8:" + Format.formatDateDay(new Date()));
		values.add(tbTrainInfo.getLevel());
		//System.out.println("9:级别" + tbTrainInfo.getLevel());
		if (tbTrainInfo.getTop() == null) {
			tbTrainInfo.setTop("n");
		} else {
			tbTrainInfo.setTop("y");
		}
		values.add(tbTrainInfo.getTop());
		//System.out.println("10:置顶：" + tbTrainInfo.getTop());
		try {
			this.myJdbcSetValuesAndExecuteSaveOrUpdateOrDelete(sql, values);
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			this.close();
		}
		sql = "select max(id) as _id from t_train_info";
		int id = 0;
		try {
			this.myJdbcSetValuesAndExecuteQuery(sql, null);
			if (rs.next()) {
				id = rs.getInt("_id");
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			this.close();
		}
		return id;
	}

	/**
	 * 更新培训资讯
	 * 
	 * @param tbTrainInfo
	 * @return
	 */
	public void update(TTrainInfo tbTrainInfo) throws Exception {
		String sql = "update  t_train_info set title=?,content=?,add_time=?,category_id = ?,keyword_1 = ?,keyword_2 = ?,keyword_3 = ?, level = ?, top = ? where id=?";
		List<Object> values = new ArrayList<Object>();
		values.add(tbTrainInfo.getTitle());
		values.add(tbTrainInfo.getContent());
		values.add(tbTrainInfo.getAddTime());
		values.add(tbTrainInfo.gettCategory().getId());
		values.add(tbTrainInfo.getKeyword1());
		values.add(tbTrainInfo.getKeyword2());
		values.add(tbTrainInfo.getKeyword3());
		values.add(tbTrainInfo.getLevel());
		if (tbTrainInfo.getTop() == null) {
			tbTrainInfo.setTop("n");
		} else {
			tbTrainInfo.setTop("y");
		}
		values.add(tbTrainInfo.getTop());
		values.add(tbTrainInfo.getId());
		try {
			this.myJdbcSetValuesAndExecuteSaveOrUpdateOrDelete(sql, values);
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			this.close();
		}
	}

	/**
	 * 删除培训资讯
	 * 
	 * @param id
	 * @return
	 */
	public void delete(int id) throws Exception {
		String sql = "delete from t_train_info  where id=?";
		try {
			this.myJdbcSetValuesAndExecuteDelete(sql, id);
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			this.close();
		}
	}

	public static List<TTrainInfo> index(int count) {
		String sql = "select * from t_train_info tti where tti.top='y' order by id desc limit "
				+ count + "";
		Connection conn = DBManager.getConnection();
		List<TTrainInfo> resultTop = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			resultTop = _resultSetToList(rs);

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (ps != null)
					ps.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
			}
		}
		List<TTrainInfo> result = null;
		if (resultTop == null || resultTop.size() < count) {
			result = new ArrayList<TTrainInfo>(resultTop);
			sql = "select * from t_train_info tti where tti.top='n' order by id desc limit "
					+ (count - resultTop.size()) + "";
			conn = DBManager.getConnection();
			try {
				ps = conn.prepareStatement(sql);
				rs = ps.executeQuery();
				result.addAll(_resultSetToList(rs));
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				try {
					if (rs != null)
						rs.close();
					if (ps != null)
						ps.close();
					if (conn != null)
						conn.close();
				} catch (SQLException e) {
				}
			}
		} else {
			result = new ArrayList<TTrainInfo>(resultTop);
		}
		return result;
	}
	
	public static List<TTrainInfo> detailTypeIndex(String typeName) {
		String sql = "select * from t_train_info tti where tti.category_id in (select tc.id from t_category tc where tc.name = '"+typeName+"') order by id desc limit 5";
		Connection conn = DBManager.getConnection();
		List<TTrainInfo> result = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			result = _resultSetToList(rs);
	
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (ps != null)
					ps.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
			}
		}
		return result;
	}

	public void list(QueryResult queryResult, TTrainInfo tTrainInfo)
			throws Exception {
		StringBuffer sql = new StringBuffer(
				"select * from t_train_info tti where 1=1");
		List<Object> values = new ArrayList<Object>();
		if (tTrainInfo != null) {
			if(tTrainInfo.getTitle() != null && tTrainInfo.getTitle().trim().length() > 0) {
				sql.append(" and tti.title like ?");
				values.add("%"+tTrainInfo.getTitle()+"%");
			}
			if (tTrainInfo.gettCategory() != null) {
				if (tTrainInfo.gettCategory().getName() != null
						&& tTrainInfo.gettCategory().getName().trim().length() > 0) {
					sql
							.append(" and tti.category_id in (select tc.id from t_category tc where tc.name = ?)");
					values.add(tTrainInfo.gettCategory().getName());
				}
			}
		}
		sql.append(" limit ?,?");
		List<Object> countValues = new ArrayList<Object>(values);
		values.add(queryResult.getStartRow());
		values.add(queryResult.getPageSize());
		try {
			this.myJdbcSetValuesAndExecuteQuery(sql.toString(), values);
			queryResult.setResultList(_resultSetToList(rs));
		} finally {
			close();
		}
		sql.replace(7, 8, "count(*)");
		try {
			this.myJdbcSetValuesAndExecuteQuery(sql.substring(0,
					sql.length() - 10), countValues);
			if (rs.next()) {
				queryResult.setAllCount(new Long(rs.getInt(1)));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			close();
		}
	}

	public TTrainInfo info(int id) throws Exception {
		String sql = "select * from t_train_info tp where tp.id = ?";
		TTrainInfo result = null;
		try {
			this.myJdbcSetValuesAndExecuteLoad(sql, id);
			result = _resultSetToListForInfo(rs);
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			close();
		}
		if (result != null && result.getId() > 0) {
			return result;
		}
		return null;
	}

	public static List<TTrainInfo> _resultSetToList(ResultSet rs)
			throws Exception {
		List<TTrainInfo> result = new ArrayList<TTrainInfo>();
		TCategoryDao tCategoryDao = new TCategoryDao();
		try {
			while (rs.next()) {
				TTrainInfo tTrainInfo = new TTrainInfo();
				tTrainInfo.setId(rs.getInt("id"));
				tTrainInfo.setLevel(rs.getString("level"));
				tTrainInfo.setTop(rs.getString("top"));
				int categoryId = rs.getInt("category_id");
				tTrainInfo.settCategory(tCategoryDao.info(categoryId));
				tTrainInfo.setAddTime(Format.formatDateDay(rs
						.getDate("add_time")));
				tTrainInfo.setTitle(rs.getString("title"));
				result.add(tTrainInfo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception();
		}
		return result;
	}

	public static TTrainInfo _resultSetToListForInfo(ResultSet rs)
			throws Exception {
		TTrainInfo tTrainInfo = new TTrainInfo();
		TCategoryDao tCategoryDao = new TCategoryDao();
		TUserDao tUserDao = new TUserDao();
		try {
			while (rs.next()) {
				tTrainInfo.setId(rs.getInt("id"));
				tTrainInfo.setLevel(rs.getString("level"));
				tTrainInfo.setTop(rs.getString("top"));
				int categoryId = rs.getInt("category_id");
				int userId = rs.getInt("user_id");
				tTrainInfo.settCategory(tCategoryDao.info(categoryId));
				tTrainInfo.settUser(tUserDao.info(userId));
				tTrainInfo.setAddTime(Format.formatDateDay(rs
						.getDate("add_time")));
				tTrainInfo.setTitle(rs.getString("title"));
				tTrainInfo.setContent(rs.getString("content"));
				tTrainInfo.setKeyword1(rs.getString("keyword_1"));
				tTrainInfo.setKeyword2(rs.getString("keyword_2"));
				tTrainInfo.setKeyword3(rs.getString("keyword_3"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception();
		}
		return tTrainInfo;
	}

}
